﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;

namespace LUStationery.DAO
{
    public class UserDAO
    {
        SqlConnection cn;
        SqlCommand cmd;
        public UserDAO() 
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }

        public IDataReader findByUserName(string username)
        {
            
            cmd = new SqlCommand("Select * from users where User_Name=@username", cn);
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getUserByEmail(string email)
        {

            cmd = new SqlCommand("Select * from users where email=@email", cn);
            cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getUsersByDepartmentAndPosition(string deptCode, string position)
        {
            cmd = new SqlCommand("select * from users where position=@position and department_code=@deptCode", cn);
            cmd.Parameters.Add("@position", SqlDbType.VarChar).Value = position;
            cmd.Parameters.Add("@deptCode", SqlDbType.VarChar).Value = deptCode;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }
        //public IDataReader getUsers(string depart, string position)
        //{
        //    cmd = new SqlCommand("select * from aspnet_Users where position=@position and department_name=@department_name", cn);
        //    cmd.Parameters.Add("@position", SqlDbType.VarChar).Value = position;
        //    cmd.Parameters.Add("@department_name", SqlDbType.VarChar).Value = depart;
        //    cn.Open();
        //    IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //    return idr;
        //}
        public IDataReader getUserNameByDeptCode(string deptCode)
        {
            cmd = new SqlCommand("select * from users where department_code=@deptCode", cn);
            cmd.Parameters.Add("@deptCode", SqlDbType.VarChar).Value = deptCode;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getUsersByDeptName(string deptName)
        {
            cmd = new SqlCommand("select * from users where department_name=@deptName", cn);
            cmd.Parameters.Add("@deptName", SqlDbType.VarChar).Value = deptName;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getUsersByDepartment(string deptCode)
        {
            cmd = new SqlCommand("select * from users where department_code=@deptCode", cn);
            cmd.Parameters.Add("@deptCode", SqlDbType.VarChar).Value = deptCode;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getUserByUserID(string staffID)
        {
            cmd = new SqlCommand("select * from users where staff_id=@staffID", cn);
            cmd.Parameters.Add("@staffID", SqlDbType.VarChar).Value = staffID;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getUserByUserName(string username)
        {
            cmd = new SqlCommand("select * from users where user_name=@username", cn);
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }
    }
}
